package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Attachment;
import com.hnl.util.FormatUtil;

public class DBAttachmentDAO implements AttachmentDAO {

	public static final Logger LOG = LoggerFactory
			.getLogger(DBAttachmentDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;

	protected DBAttachmentDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public Attachment saveAttachment(Attachment attachment) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO ATTACHMENT (talent_ID, attachment_type, content_type, system_path, isdelete, create_date, create_user, modify_date, modify_user) "
									+ "VALUES (?,?,?,?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setInt(1, attachment.getTalentId());
			saveStmt.setInt(2,
					(attachment.getAttachmentType() == null || attachment
							.getAttachmentType() == 0) ? Integer.valueOf(1)
							: attachment.getAttachmentType());
			saveStmt.setString(3, attachment.getContentType());
			saveStmt.setString(4, attachment.getSystemPath());
			saveStmt.setBoolean(5, attachment.getIsDelete());
			saveStmt.setString(6,
					FormatUtil.getXmlDate(attachment.getCreateDate()));
			saveStmt.setInt(7, attachment.getCreateUser());
			saveStmt.setString(8,
					FormatUtil.getXmlDate(attachment.getModifyDate()));
			saveStmt.setInt(9, attachment.getModifyUser());

			saveStmt.executeUpdate();
			ResultSet rs = saveStmt.getGeneratedKeys();
			while (rs.next()) {
				attachment.setAttachmentId(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving Attachment.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return attachment;
	}

	public Collection<Attachment> loadAttachement(Integer talentId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Collection<Attachment> talentAttachment = new Vector<Attachment>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT attachment_ID, talent_ID, attachment_type, content_type, system_path, isdelete, create_date, create_user, modify_date, modify_user FROM ATTACHMENT WHERE talent_ID=? AND isdelete=false order by modify_date desc");
			loadStmt.setInt(1, talentId);
			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				Attachment attachment = new Attachment();
				attachment.setAttachmentId(rs.getInt(1));
				attachment.setTalentId(rs.getInt(2));
				attachment.setAttachmentType(rs.getInt(3));
				attachment.setContentType(rs.getString(4));
				attachment.setSystemPath(rs.getString(5));
				attachment.setIsDelete(rs.getBoolean(6));
				attachment.setCreateDate(rs.getDate(7));
				attachment.setCreateUser(rs.getInt(8));
				attachment.setModifyDate(rs.getDate(9));
				attachment.setModifyUser(rs.getInt(10));
				talentAttachment.add(attachment);
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading talent attachments", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return talentAttachment;
	}

	public void deleteAttachement(Integer attachementId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			saveStmt = conn
					.prepareStatement("UPDATE ATTACHMENT SET isdelete=true where attachment_ID=?");
			saveStmt.setInt(1, attachementId);
			saveStmt.executeUpdate();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error Delete Attachment.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

}
